package cn.edu.scau.cmi.lijiongtao.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.cell.ChoiceBoxListCell;
import javafx.scene.control.cell.TextFieldListCell;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.layout.GridPane;
import jdk.internal.org.objectweb.asm.tree.IntInsnNode;

public class Teacher  extends GridPane{
	//声明连接对象
	private Connection connection = null;
	//驱动程序名
	private String driver = "com.mysql.jdbc.Driver";
	//URL
	private String url = "jdbc:mysql://localhost:3306/program6";
	//User
	private String user = "root";
	private String password = "ljt123456";
	// 登陆人信息
	private String olName = "";
	private String olId = "";
	//students
	private ArrayList<String> sID = new ArrayList<String>();
	private ArrayList<String> sName = new ArrayList<String>();
	
	public Teacher(String id, String name) {
		this.olId = id;
		this.olName = name;
		init();
	}
	
	private void init() {
		/**************定义控件**************/
		Label lbTea = new Label("教师：");
		Label txtTea = new Label(this.olName);
		Label lbStu = new Label("指导的学生：");
		Button btChange = new Button("修改");
		Button btAdd = new Button("添加");
		Button btOK = new Button("确认添加");
		ListView<String> list = new ListView<String>();
		ObservableList<String> items = FXCollections.observableArrayList();
		ObservableList<String> allStu = FXCollections.observableArrayList();
		//读取数据库内容并显示
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
			boolean flag = false;
			if (!connection.isClosed()) {
				Statement statement1 = connection.createStatement();
				Statement statement2 = connection.createStatement();
				// SQL
				String sql1 = "select * from student";
				ResultSet rSet = statement1.executeQuery(sql1);
				while(rSet.next()) {
					allStu.add(rSet.getString("name"));
				}
				sql1 = "select * from teach where t_id = " + olId;
				rSet = statement1.executeQuery(sql1);
				while (rSet.next()) {
					sID.add(rSet.getString("s_id"));
					String sql = "select * from student where id = " + rSet.getString("s_id");
					ResultSet rs = statement2.executeQuery(sql);
					String user = "";
					while (rs.next()) {
						user = rs.getString("name");
						sName.add(user);
						items.add(user);
						break;
					}
					rs.close();
				}
				rSet.close();
				connection.close();
			}
		} catch (ClassNotFoundException e2) {
			System.out.println("数据库驱动异常");
		} catch (SQLException e3) {
			e3.printStackTrace();
		} 
		//设置控件
		list.setItems(items);
		list.setCellFactory(TextFieldListCell.forListView());
		list.setEditable(true);
		//将控件添加到面板
		this.addRow(2, lbTea, txtTea);
		this.addRow(4, lbStu, list);
		this.addRow(6, btChange, btAdd, btOK);
		
		/***************************************按钮事件*********************/
		//确认添加学生
		btOK.setOnAction(e -> {
			list.setItems(items);
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					// SQL
					String sql = "select * from student where name = '" + items.get(items.size()-1) + "';";
					ResultSet rs = statement.executeQuery(sql);
					String tempID = "";
					if(rs.next()) {
						tempID = rs.getString("id");
						sql = "insert into teach(s_id,t_id) values(" + tempID + "," + olId + ");";
						statement.executeUpdate(sql);
						sID.add(tempID);
						sName.add(items.get(items.size()-1));
						Alert alert = new Alert(AlertType.INFORMATION);
						alert.setTitle("提示");
						alert.setHeaderText(null);
						alert.setContentText("添加成功！");
						alert.showAndWait();
					}
					else {
						Alert alert = new Alert(AlertType.ERROR);
						alert.setTitle("错误");
						alert.setHeaderText(null);
						alert.setContentText("不存在该用户！");
						alert.showAndWait();
					}
					rs.close();
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		});
		//添加学生
		btAdd.setOnAction(e -> {
			items.add("双击后在此处输入学生名字");
			list.setItems(items);
			
		});
		//修改指导的学生
		btChange.setOnAction(e -> {
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
				if (!connection.isClosed()) {
					Statement statement = connection.createStatement();
					// SQL
					String sql = "";
					ResultSet rs;
					String tempID = "";
					boolean flag = false;
					for(int i = 0; i < sName.size(); i++) {
						if(!sName.get(i).equals(items.get(i))) {
							//System.out.println(sName.get(i));
							//System.out.println(items.get(i));
							sql = "select * from student where name = '" + items.get(i) + "';";
							rs = statement.executeQuery(sql);
							if(rs.next()) {
								//System.out.println("change");
								tempID = rs.getString("id");
								//System.out.println(sID.get(i));
								sql = "update teach set s_id = '" + tempID + "' where t_id = '" + olId + "' and s_id = '" + sID.get(i) + "';";
								System.out.println(sql);
								statement.executeUpdate(sql);
								sID.set(i, tempID);
								sName.set(i, items.get(i));
								flag = true;
								//System.out.println(sName.get(i));
								//System.out.println(sID.get(i));
							}
							else {
								flag = false;
								System.out.println("not found");
								break;
							}
							rs.close();
						}
					}
					if(flag) {
						Alert alert = new Alert(AlertType.INFORMATION);
						alert.setTitle("提示");
						alert.setHeaderText(null);
						alert.setContentText("修改成功！");
						alert.showAndWait();
					}
					else {
						Alert alert = new Alert(AlertType.ERROR);
						alert.setTitle("错误");
						alert.setHeaderText(null);
						alert.setContentText("不存在该用户！");
						alert.showAndWait();
					}
						
					connection.close();
				}
			} catch (ClassNotFoundException e2) {
				System.out.println("数据库驱动异常");
			} catch (SQLException e3) {
				e3.printStackTrace();
			} 
		});
	}
			
}
